from urllib.parse import quote_plus
from sqlalchemy import func,create_engine, Column, Sequence, Integer, MetaData, String, ForeignKeyConstraint
from sqlalchemy.ext.declarative import declarative_base, DeferredReflection
from sqlalchemy.orm import sessionmaker, relationship
from pprint import pprint
from datetime import date




connection_string = "Trusted_Connection=yes;DRIVER={SQL Server Native Client 11.0};SERVER=srvsql12beta;Database=dw_powercard"
connection_string = quote_plus(connection_string) 
connection_string = "mssql+pyodbc:///?odbc_connect=%s" % connection_string

engine = create_engine(connection_string)

meta = MetaData(bind=engine)
#meta.reflect(only=['ACCOUNTS_LINK','V_AUTHO_ACTIVITY_VIEW', 'CARD_PRODUCT', 'CARD'])
meta.reflect(only=['V_AUTHO_ACTIVITY_VIEW', 'CARD_PRODUCT'])
#al = meta.tables['ACCOUNTS_LINK']


'''
class VAUTH():
    __table__ = meta.tables['V_AUTHO_ACTIVITY_VIEW']
    def __init__(self):
        for cname in VAUTH.__table__.c.keys():
            setattr(self,cname, VAUTH.__table__.c[cname])


v = VAUTH()
'''

v = meta.tables['V_AUTHO_ACTIVITY_VIEW']
cp = meta.tables['CARD_PRODUCT']
#c = meta.tables['CARD']
Session = sessionmaker()
session = Session(bind=engine)
session = Session()
#r=session.query(v.c.CARD_ACCEPTOR_ID,v.c.TRANSACTION_LOCAL_DATE,v.c.CARD_NUMBER,v.c.BILLING_AMOUNT,v.c.SOURCE_ACCOUNT_NUMBER,cp.c.PRODUCT_TYPE,cp.c.WORDING).join(cp,v.c.PRODUCT_CODE==cp.c.PRODUCT_CODE).limit(1).first()
accounts = session.query(v.c.CARD_NUMBER).limit(4)
d1 = date(year=2012,month=1,day=1)
d2 = date(year=2012,month=7,day=1)
r=session.query(v.c.CARD_NUMBER,func.sum(v.c.BILLING_AMOUNT)).group_by(v.c.CARD_NUMBER).filter(v.c.TRANSACTION_LOCAL_DATE.between(d1,d2), v.c.CARD_NUMBER.in_(accounts)).all()

